set more off
local a 55
local y 2013

*** OUTCOMES FROM FLAT FILE FOR AGE 55
use "Data/NCDS/UKDA-7669-stata11/stata11/ncds_2013_flatfile.dta", clear
merge  1:1 NCDSID using "Data/NCDS/UKDA-7669-stata11/stata11/ncds_2013_derived.dta", nogenerate
merge  1:1 NCDSID using scratchdata/ncds_2013_unfolding.dta, nogenerate

* sex
rename  N9CMSEX sex_`a'

* merge RPI
ge year=`y' /* note 2013 is base year for prices*/
merge m:1 year using scratchdata/RPI.dta, keep(match) nogenerate

** Observation indicator
ge Obs_55=1

rename ND9REGN region_`a'

********************************************************************************
* Part 1: pay period for employed
********************************************************************************

* net
ge pp_nee=N9NETP 
recode pp_nee   (-1=.) (5=4.33) (10=13) (11=26) (12=52) (13=1) (14=.) (15=.)
replace pp_nee =4.33*2 if pp_nee==. & N9NETP3==6
replace pp_nee =1 if pp_nee==. & N9NETP3==13

* gross
ge pp_gee=N9GROP
recode pp_gee (-1=.) (5=4.33) (10=13) (11=26) (12=52) (13=1) (14=.) (15=.)

* usual net
ge pp_unee=N9USLP
recode pp_unee  (-1=.) (5=4.33) (10=13) (11=26) (12=52) (13=1) (14=.) (15=.)

********************************************************************************
* Part 2: pay for employed
********************************************************************************

* gross
ge gpee_`a'=N9GROA if N9GROA>=0

* net
ge npee_`a'=N9NETA if N9NETA>=0

* usual net
ge unpee_`a'=N9USLA if N9USLA>=0

********************************************************************************
* Part 3: pay per week for employed
********************************************************************************

* gross
replace gpee_`a'=gpee_`a'/pp_gee

* net
replace npee_`a'=npee_`a'/pp_nee

* usual net
replace unpee_`a'=unpee_`a'/pp_unee

********************************************************************************
* Part 4: hours per week for employed
********************************************************************************

ge hoursee_`a'=N9CHORS1 if N9CHORS1>=0

********************************************************************************
* Part 5: weekly earnings for self-employed
********************************************************************************

ge ese_`a'=N9SEPA if N9SEPA>=0
ge npse_`a'=. /* inforamtion not available */

********************************************************************************
* Merge earnings information from unfolding brackets
********************************************************************************

local vars g n un
foreach v of local vars{
replace `v'pee_min=`v'pee_min/pp_`v'ee
replace `v'pee_max=`v'pee_max/pp_`v'ee
}

* apply interval regression to bounds from unfolding brackets
local vars gpee npee unpee ese
foreach v of local vars{
replace `v'_min=0 if `v'_min==. & `v'_max!=.
replace `v'_max=. if `v'_max==. & `v'_min!=.
replace `v'_max=`v'_max+.01 if `v'_max==`v'_min
intreg `v'_min `v'_max
predict p`v' if `v'_min!=., ystar(`v'_min,`v'_max)
su p`v'
replace `v'_`a'=p`v' if p`v'!=.
drop p`v'
drop `v'_min `v'_max
}

* convert self employment income to weeks
replace ese_`a'=ese_`a'/52 

* employed: usual net pay
replace npee_`a'=unpee_`a' if unpee_`a'!=.
drop unpee_`a' 

********************************************************************************
* Part 6: hours per week for self-employed
********************************************************************************

ge hoursse_`a'=N9CJSEHS if N9CJSEHS>=0

********************************************************************************
* Part 7: inflation adjustment
********************************************************************************

local vars npee_`a' gpee_`a' ese_`a' npse_`a'
foreach v of local vars {
replace `v'=`v'/rpi
}

********************************************************************************
* Part 8: Economic activity
********************************************************************************

rename ND9ECACT EconAct
replace EconAct=. if EconAct<0

ge EE_`a'=(EconAct==1|EconAct==2) if EconAct!=. 
ge SE_`a'=(EconAct==3|EconAct==4) if EconAct!=. 

* clasify as non-working if report zero earnings
replace EE_`a'=0 if npee_`a'==0 & EE_`a'!=.
replace SE_`a'=0 if ese_`a'==0  & SE_`a'!=.
ge AE_`a'=EE_`a'+SE_`a'

ge UN_`a'=EconAct==5 if EconAct!=.

********************************************************************************
* Part 9: recode hours
********************************************************************************

* recode hours to missing if hours>112 or zero
replace hoursee_`a'=. if (hoursee_`a'>112|hoursee_`a'==0)
replace hoursse_`a'=. if (hoursse_`a'>112|hoursse_`a'==0)

replace hoursee_`a'=40 if EconAct==1 & hoursee_`a'==.
replace hoursee_`a'=20 if EconAct==2 & hoursee_`a'==.
replace hoursse_`a'=40 if EconAct==3 & hoursse_`a'==.
replace hoursse_`a'=20 if EconAct==4 & hoursse_`a'==.

replace hoursee_`a'=. if  npee_`a'==.
replace hoursse_`a'=. if  ese_`a'==.

* recode earnings and hours as missing if don't match current economic activity
replace npee_`a'=. if EE_`a'!=1
replace gpee_`a'=. if EE_`a'!=1
replace hoursee_`a'=. if  EE_`a'!=1
replace ese_`a'=. if SE_`a'!=1
replace hoursse_`a'=. if SE_`a'!=1

********************************************************************************
* Part 10: hourly wages
********************************************************************************

* employed net
ge wageee_`a'=npee_`a'/hoursee_`a'

* employed gross
ge gwageee_`a'=gpee_`a'/hoursee_`a'

* self-employed (earnings)
ge wagese_`a'=ese_`a'/hoursse_`a'

* self-employed (profit) 
ge prose_`a'=npse_`a'/hoursse_`a'


********************************************************************************
* Part 11: combine employed and self-employed
********************************************************************************

egen wage_`a'=rowmax(wageee_`a' wagese_`a')
drop wageee_`a' wagese_`a'

egen pay_`a'=rowmax(npee_`a' ese_`a')
drop npee_`a' ese_`a'

egen hours_`a'=rowmax(hoursee_`a' hoursse_`a')
drop hoursee_`a' hoursse_`a'


********************************************************************************
*  Part 12: log variables
********************************************************************************

local vars wage pay hours
foreach v of local vars {
ge l`v'_`a'=ln(`v'_`a')
}

********************************************************************************
* Part 13: SOC 2000 and 2100
********************************************************************************

ge soc00_`a'=N9CS2000 if N9CS2000>0
replace soc00_`a'=. if AE_`a'!=1

ge soc10_`a'=N9CS2010 if N9CS2010>0
replace soc10_`a'=. if AE_`a'!=1



********************************************************************************
* Part 14: Occupational attainment (Social class 1990 based on soc2010)
********************************************************************************

rename N9CSC SC_`a'
replace SC_`a'=. if SC_`a'<1|SC_`a'==6|AE_`a'!=1


********************************************************************************
* Part 15: Job security
********************************************************************************

rename N9JSECUE JobSecurity_`a'
replace JobSecurity_`a'=. if JobSecurity_`a'<0|AE_`a'!=1
ge JobSecurityHi_`a'=JobSecurity_`a'==1 if JobSecurity_`a'!=.
ge JobSecurityMedHi_`a'=JobSecurity_`a'==1|JobSecurity_`a'==2 if JobSecurity_`a'!=.

********************************************************************************
* Part 16: Industry
********************************************************************************

rename N9CSIC Ind_`a' 				/* 2007 code */
replace Ind_`a'=. if Ind_`a'<0|AE_`a'!=1

********************************************************************************
* Finish up
********************************************************************************

rename N9TRNWKA N9TRNWKA_`a'
rename N9TRANWK  N9TRANWK_`a'

keep NCDSID *_`a'
sort NCDSID
rename *_`a' *_I`a'

save scratchdata/ncds_2013_flatfile.dta, replace
